package kjh820909.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import kjh820909.model.MemberVO;
import kjh820909.utils.OracleJDBC;


public class MemberDAO {
	private final String insertSQL = 
			"insert into memberv1(mno,userid,pwd,name,gender)"
            + " VALUES(mbseq.nextval,?,?,?,?)";
	private final String updateSQL = 
			"update memberv1 set name = ?, pwd =?, regdate = sysdate where mno = ? ";
	private final String deleteSQL = 
			"delete from memberv1 where mno = ?";
	private final String selectSQL = 
			"select mno, userid, name, regdate from memberv1 order by mno desc";
	private final String selectOneSQL = 
			"select * from memberV1 where mno = ? ";
	
	// 회원가입 insert
	public int insertMember(MemberVO m){
		Connection conn = null;
		PreparedStatement ps =null;
		int cnt = 0;
		try{
			conn = OracleJDBC.openConn();
			ps = conn.prepareStatement(insertSQL);
			ps.setString(1, m.getUserid());
			ps.setString(2, m.getPwd());
			ps.setString(3, m.getName());
			ps.setString(4, m.getGender());
			cnt = ps.executeUpdate();
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			OracleJDBC.closeConn(conn, ps, null);
		}
		return cnt;
	}
	
	// 회원수정 update
	public int updateMember(MemberVO m){
		Connection conn = null;
		PreparedStatement ps = null;
		int cnt = 0;
		try{
			conn = OracleJDBC.openConn();
			ps = conn.prepareStatement(updateSQL);
			ps.setString(1, m.getName());
			ps.setString(2, m.getPwd());
			ps.setString(3, m.getMno());
			cnt = ps.executeUpdate();
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			OracleJDBC.closeConn(conn, ps, null);
		}
		
		return cnt;
	}
	
	// 회원삭제 delete
	public int deleteMember(String mno){
		Connection conn = null;
		PreparedStatement ps = null;
		int cnt = 0;
		try{
			conn = OracleJDBC.openConn();
			ps = conn.prepareStatement(deleteSQL);
			ps.setString(1, mno);
			cnt = ps.executeUpdate();
			
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			OracleJDBC.closeConn(conn, ps, null);
		}
		return cnt;
	}
	
	// 회원 전체조회
	public ArrayList<MemberVO> selectMember(){
		ArrayList<MemberVO>list
				= new ArrayList<>();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try{
			conn = OracleJDBC.openConn();
			ps = conn.prepareStatement(selectSQL);
			rs = ps.executeQuery();
			while(rs.next()){
				MemberVO m = new MemberVO(rs.getString(1)
						,rs.getString(2), null, rs.getString(3), null,
						rs.getString(4));
				list.add(m);
			}
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			OracleJDBC.closeConn(conn, ps, rs);
		}
		
		return list;
	}
	
	// 회원 상세조회
	public MemberVO selectOneMember(String mno){
		MemberVO m = null;
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			conn = OracleJDBC.openConn();
			ps = conn.prepareStatement(selectOneSQL);
				ps.setString(1, mno);
			rs = ps.executeQuery();
			rs.next();
			m = new MemberVO(rs.getString(1),rs.getString(2),
					rs.getString(3),rs.getString(4),
					rs.getString(5),rs.getString(6));
			
			
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			OracleJDBC.closeConn(conn, ps, rs);
		}
		
		return m;
	}
	
}
